This notebook includes 4 strategies used for data cleaning as follows:¶
In [13]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import datetime
import sklearn
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import cross_val_score
from sklearn.linear_model import LassoCV
import re
from IPython.display import display
#import vaex
Changed working directory path to where my data is saved.¶
In [5]:
import os
print(os.getcwd())
os.chdir("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips")
In [3]:
#
pd.options.display.max_columns = None
pd.options.display.max_rows = None
In [278]:
months = ['jan','feb','mar','apr','may','jun','july','aug','sep','oct','nov1','nov15','nov19','dec']
la = 'la_'
names = {}
for i in months:
names[la +i] = pd.read_csv("listings_" + i +".csv")
In [ ]:
spring_listing
Given that the objective of this project is predicting listing price using various machin learning models instead of predicting booked rate, I put calendar dataset aside for now and may come back to study it later.¶
2020 summer¶
In [385]:
cal = 'cal_'
current = {}
for i in summer:
current[cal+i] = pd.read_csv("calendar_2020_" + i +".csv")
In [387]:
weekDays = ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
In [388]:
for i in current:
current[i]['date'] = pd.to_datetime(current[i]['date'])
current[i]['weekday'] = current[i]['date'].apply(lambda x : x.weekday())
current[i]['weekdays'] = current[i]['weekday'].apply(lambda x : weekDays[x])
In [ ]:
In [390]:
current_keys = list(current.keys())
current_summer = current['cal_july']
for i in current_keys:
current_summer= pd.concat([current_summer,current[i]], axis = 0)
In [391]:
current_summer.shape
Out[391]:
In [460]:
current_summer.shape
Out[460]:
In [394]:
current_summer.date = current_summer.date.dt.month
In [395]:
current_summer.date.value_counts()
Out[395]:
In [396]:
current_df = current_summer[(current_summer['date'] == 7) | (current_summer['date'] == 8) | (current_summer['date'] == 9)]
In [397]:
current_df.shape
Out[397]:
In [398]:
current_df.date.value_counts()
Out[398]:
In [466]:
current_df.isnull().sum()
Out[466]:
In [399]:
current_df = current_df.dropna()
In [400]:
current_df.shape
Out[400]:
In [ ]:
In [401]:
current_df['price'] = current_df['price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
current_df['adjusted_price'] = current_df['adjusted_price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [402]:
current_df['available'] = current_df['available'].map(dict(f=1,t=0))
In [404]:
current_df.head()
Out[404]:
In [403]:
current_df.shape
Out[403]:
In [405]:
print(os.getcwd())
In [406]:
current_df.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_2020.csv')
In [ ]:
In [421]:
current_booked = current_df.groupby('date').agg({'available':sum})
In [415]:
current_listings = current_df.groupby('date')['price'].count().to_frame()
In [417]:
current_listings = current_listings.reset_index()
In [419]:
current_listings
Out[419]:
In [423]:
current_booked = current_booked.reset_index()
In [424]:
current_booked
Out[424]:
In [425]:
current_booked['booked_rate'] = round(current_booked['available'] / current_listings['price'],3)
In [429]:
current_booked_avg_price = current_df.groupby('date').agg({'price': mean})
In [431]:
current_booked_avg_price.reset_index(inplace = True)
In [434]:
current_booked_avg_price.price = round(current_booked_avg_price.price,2)
In [435]:
current_booked['avg_price'] = current_booked_avg_price.price
In [439]:
current_booked.rename(columns = {'available':'booked'},inplace= True)
In [441]:
print(os.getcwd())
In [443]:
current_booked.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_2020_booked_rate.csv')
In [479]:
current_booked
Out[479]:
In [492]:
current_weekdays_booked = current_df.groupby('weekdays').agg({'available':sum})
In [493]:
current_weekdays_listings = current_df.groupby('weekdays')['price'].count().to_frame()
In [494]:
current_weekdays_booked['booked_rate'] =round(current_weekdays_booked['available'] / current_weekdays_listings['price'],3)
This is 2020 summer weekdays booked rate¶
In [496]:
current_weekdays_booked.reset_index(inplace = True)
In [497]:
current_weekdays_booked
Out[497]:
In [499]:
summer_week_booked['booked_rate_2020'] = current_weekdays_booked['booked_rate']
In [500]:
summer_week_booked
Out[500]:
In [502]:
summer_week_booked.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_week_2019_2020.csv')
In [501]:
print(os.getcwd())
In [493]:
current_weekdays_booked.sort_values('booked_rate',ascending =False)a
Out[493]:
In [494]:
summer_week_booked
In [ ]:
In [ ]:
only half a year¶
In [179]:
half_year= ['aug','sep','oct','nov1','nov15','nov19','dec','jan']
In [384]:
summer = ['july','aug','sep']
In [5]:
spring = ['jan','feb','mar']
In [333]:
fall = ['apr','may','jun']
In [335]:
calfall = 'fall_'
fall_month = {}
for i in fall:
fall_month[calfall +i] = pd.read_csv("calendar_"+i+".csv")
In [338]:
for i in fall_month:
fall_month[i]['date'] = pd.to_datetime(fall_month[i]['date'])
fall_month[i]['weekday'] = fall_month[i]['date'].apply(lambda x : x.weekday())
fall_month[i]['weekdays'] = fall_month[i]['weekday'].apply(lambda x : weekDays[x])
Concatenate three fall tables togethe to one data frame named fall_df¶
In [340]:
fall_month.keys()
Out[340]:
In [342]:
fall_keys = list(fall_month.keys())
fall_df = fall_month['fall_apr']
for i in fall_keys:
fall_df = pd.concat([fall_df,fall_month[i]], axis = 0)
In [344]:
fall_df.shape
Out[344]:
In [345]:
fall_df = fall_df.dropna()
In [346]:
fall_df.date = fall_df.date.dt.month
In [348]:
fall_df = fall_df[fall_df['date'].isin([4,5,6])]
In [350]:
fall_df.date.value_counts()
Out[350]:
In [351]:
fall_df.price = fall_df.price.astype(str).str.replace("$","").str.replace(",","").astype(float)
fall_df.adjusted_price = fall_df.adjusted_price.astype(str).str.replace("$","").str.replace(",","").astype(float)
In [352]:
fall_df.available = fall_df.available.map(dict(f=1,t=0))
In [353]:
fall_df.head()
Out[353]:
In [367]:
fall_cleaned = fall_df
In [368]:
fall_cleaned.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/fall_cleaned.csv', index = False)
In [356]:
fall_booked = fall_df.groupby('date').agg({'available':sum})
In [357]:
fall_listings = fall_df.groupby('date')['price'].count().to_frame()
In [358]:
fall_booked['booked_rate'] = round(fall_booked['available'] / fall_listings['price'],3)
In [359]:
fall_booked
Out[359]:
Concatenate 12 months of calendar dataset together as one dateframe named calendar_2019¶
In [423]:
winter = pd.read_csv('winter_cleaned_updated.csv')
In [429]:
spring = pd.read_csv('spring_cleaned.csv')
In [431]:
summer = pd.read_csv('summer_cleaned.csv')
In [426]:
fall = pd.read_csv('fall_cleaned.csv')
In [362]:
winter.head()
Out[362]:
In [425]:
fall = fall_cleaned
In [365]:
spring.head()
Out[365]:
In [444]:
summer.head()
In [432]:
summer_spring = spring.append(summer)
In [433]:
summer_spring.date.value_counts()
Out[433]:
In [427]:
winter_fall = fall.append(winter)
In [398]:
winter_fall.shape
Out[398]:
In [434]:
winter_fall.date.value_counts()
Out[434]:
In [435]:
calendar_2019 = summer_spring.append(winter_fall)
In [436]:
calendar_2019.shape
Out[436]:
In [437]:
calendar_2019.date.value_counts()
Out[437]:
In [413]:
print(os.getcwd())
In [438]:
calendar_2019.to_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_2019_updated.csv")
check the price seasonality by using OLS to see their coefficients.¶
In [439]:
calendar_2019.weekdays.dtype
Out[439]:
In [440]:
calendar_2019.date = calendar_2019.date.astype('category')
In [441]:
calendar_2019.weekdays = calendar_2019.weekdays.astype('category')
In [435]:
calendar_2019.price.dtype
Out[435]:
In [442]:
calendar_booked = calendar_2019.groupby('date').agg({'available':sum})
In [443]:
calendar_listings = calendar_2019.groupby('date')['price'].count().to_frame()
calendar_price = calendar_2019.groupby('date').agg({'price':mean()})
In [464]:
calendar_listings = calendar_listings.reset_index()\
In [465]:
calendar_listings
Out[465]:
In [450]:
calendar_price = calendar_2019.groupby('date').agg({'price':mean})
In [452]:
calendar_price['price'] = round(calendar_price['price'],2)
In [457]:
calendar_price = calendar_price.reset_index()
In [444]:
calendar_booked['booked_rate'] = round(calendar_booked.available / calendar_listings.price,3)
In [445]:
calendar_booked_df = calendar_booked.reset_index()
In [459]:
calendar_booked_df['avg_price'] = calendar_price['price']
In [466]:
calendar_booked_df['listings'] = calendar_listings['price']
In [467]:
calendar_booked_df
Out[467]:
In [447]:
print(os.getcwd())
In [468]:
calendar_booked_df.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_booked_df_with_listings.csv', index = False)
In [488]:
#calendar_booked_df.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_booked_df.csv', index = False)
In [273]:
calendar_2019_booked_rate = calendar_booked_df.sort_values('booked_rate',ascending = False)
In [ ]:
In [7]:
import os
In [9]:
print(os.getcwd())
In [12]:
calendar_booked_df = pd.read_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_booked_df.csv')
calendar_2019 = pd.read_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_2019.csv')
In [104]:
calendar_2019 = pd.read_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_2019.csv')
In [296]:
.yearly_booked = calendar_2019.groupby(['date','weekdays']).agg({'available':sum})
In [387]:
calendar_2019.groupby('date')['price'].count().to_frame()
Out[387]:
In [297]:
yearly_listings = calendar_2019.groupby(['date','weekdays'])['price'].count().to_frame()
In [298]:
yearly_booked['booked_rate'] = round(yearly_booked['available'] / yearly_listings['price'],3)
In [481]:
yearly_booked_df= yearly_booked.reset_index()
In [299]:
yearly_booked_rate = yearly_booked.reset_index()
In [300]:
yearly_booked_rate
Out[300]:
In [301]:
yearly_booked_rate.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/yearly_booked_rate.csv', index = False)
In [484]:
yearly_booked_rate
Out[484]:
In [477]:
month_1 = yearly_booked_rate[yearly_booked_rate['date'] ==1].sort_values('booked_rate',ascending = False)
In [478]:
month_1
Out[478]:
In [479]:
sns.lineplot(data=month_1, x="weekdays", y="booked_rate")
Out[479]:
In [ ]:
In [307]:
calendar_2019.head()
Out[307]:
In [469]:
calendar_weekdays_booked = calendar_2019.groupby('weekdays').agg({'available':sum})
calendar_weekdays_listings = calendar_2019.groupby('weekdays')['price'].count().to_frame()
calendar_weekdays_booked['booked_rate'] = round(calendar_weekdays_booked.available / calendar_weekdays_listings.price,3)
In [483]:
calendar_weekdays_avgprice = calendar_2019.groupby('weekdays').agg({'price':mean})
In [484]:
calendar_weekdays_avgprice = calendar_weekdays_avgprice.reset_index()
In [486]:
calendar_weekdays_avgprice['price'] = round(calendar_weekdays_avgprice['price'] ,2)
In [470]:
calendar_weekdays_total= calendar_weekdays_listings.reset_index()
In [474]:
calendar_weekdays_booked = calendar_weekdays_booked.reset_index()
In [479]:
calendar_weekdays_listings = calendar_weekdays_listings.reset_index()
In [480]:
calendar_weekdays_listings
Out[480]:
In [481]:
calendar_weekdays_booked['listings'] = calendar_weekdays_listings['price']
In [487]:
calendar_weekdays_booked['avg_price'] = calendar_weekdays_avgprice['price']
In [488]:
calendar_weekdays_booked
Out[488]:
In [311]:
print(os.getcwd())
In [489]:
calendar_weekdays_booked.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/calendar_weekdays_booked_final_with_avg_price.csv', index = False)
In [105]:
calendar_2019.dropna(inplace = True)
In [491]:
month_index = calendar_2019["date"].astype('category')
In [492]:
month_index.head()
Out[492]:
Run ols on months and weekdays to check seasonality¶
In [2]:
import statsmodels.api as stat
In [493]:
month_dummies_new =pd.get_dummies(month_index)
In [495]:
month_dummies_new.head()
Out[495]:
In [490]:
month_dummies.head()
Out[490]:
In [126]:
month_dummies.head()
Out[126]:
In [91]:
calendar_months_dummies= calendar_month_dummies.drop(calendar_month_dummies.columns[[0]], axis = 1)
In [92]:
calendar_months_dummies.head()
Out[92]:
In [6]:
calendar_month_y = calendar_2019["price"]
In [499]:
calendar_month_y.shape
Out[499]:
In [500]:
month_dummies_new.shape
Out[500]:
In [84]:
if isinstance(calendar_2019, pd.DataFrame):
print('yes')
In [ ]:
month_dummies.head()
In [128]:
month_ols= stat.OLS(calendar_month_y, month_dummies)
In [ ]:
In [3]:
month_new_ols = stat.OLS(calendar_month_y, month_dummies_new)
month_new_ols_result = month_new_ols.fit()
In [ ]:
print(month_new_ols_result.summary())
In [129]:
month_ols_result = month_ols.fit()
In [130]:
print(month_ols_result.summary())
In [133]:
calendar_2019.dtypes
Out[133]:
In [134]:
booked_month = calendar_2019["available"].astype('category')
In [135]:
booked = calendar_2019["available"]
In [137]:
seasonality_booked_model= stat.OLS(booked, month_dummies)
seasonality_booked_result = seasonality_booked_model.fit()
In [138]:
print(seasonality_booked_result.summary())
In [139]:
weekdays_booked = stat.OLS(booked, weekdays_dummy)
weekdays_booked_result = weekdays_booked.fit()
In [140]:
print(weekdays_booked_result.summary())
In [ ]:
print(seasonality_booked_result.summary())
In [429]:
calendar_weekdays_dummies = pd.get_dummies(calendar_2019['weekdays'])
In [119]:
calendar_weekdays = calendar_2019['weekdays'].astype('category')
In [120]:
weekdays_dummy = pd.get_dummies(calendar_weekdays)
In [112]:
calendar_2019.columns
Out[112]:
In [121]:
weekdays_ols = stat.OLS(calendar_weekdays_y, weekdays_dummy)
weekdays_result = weekdays_ols.fit()
In [122]:
print(weekdays_result.summary())
In [113]:
weekdays_index = calendar_2019['weekday'].astype('category')
In [115]:
weekdays_dummies = pd.get_dummies(weekdays_index)
In [ ]:
In [116]:
calendar_weekdays_y = calendar_2019['price']
In [117]:
calendar_weekdays_ols = stat.OLS(calendar_weekdays_y, weekdays_dummies)
calendar_weekdays_result = calendar_weekdays_ols.fit()
In [118]:
print(calendar_weekdays_result.summary())
Check the booked rate seasonality¶
In [ ]:
In [ ]:
In [ ]:
calendar = pd.concat()
In [8]:
cal = 'cal_'
calendars = {}
for i in half_year:
calendars[cal+i] = pd.read_csv("calendar_" + i +".csv")
Study spring price pattern and booked rate trend .¶
In [297]:
calspring = 'spring_'
spring_month = {}
for i in spring:
spring_month[calspring +i] = pd.read_csv("calendar_"+i+".csv")
In [298]:
for i in spring_month:
spring_month[i]['date'] = pd.to_datetime(spring_month[i]['date'])
spring_month[i]['weekday'] = spring_month[i]['date'].apply(lambda x : x.weekday())
spring_month[i]['weekdays'] = spring_month[i]['weekday'].apply(lambda x : weekDays[x])
Concatenate three spring tables togethe to one data frame named spring_df¶
In [299]:
spring_keys = list(spring_month.keys())
spring_df = spring_month['spring_jan']
for i in spring_keys:
spring_df = pd.concat([spring_df,spring_month[i]], axis = 0)
Dropping na values.¶
In [225]:
spring_df.shape
Out[225]:
In [226]:
spring_df.isnull().sum()
Out[226]:
In [300]:
spring_nona = spring_df.dropna()
Converting datatypes to proper one¶
In [301]:
spring_nona['date'] = spring_nona['date'].dt.month
In [302]:
spring_cleaned = spring_nona[(spring_nona['date'] == 1) |(spring_nona['date'] == 2) | (spring_nona['date'] == 3)]
In [303]:
spring_cleaned['price']= spring_cleaned['price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [304]:
spring_cleaned['adjusted_price'] = spring_cleaned['adjusted_price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [305]:
spring_cleaned.available = spring_cleaned.available.map(dict(f=1,t=0))
In [306]:
spring_category = ['date','weekday']
In [307]:
spring_cleaned[spring_category] = spring_cleaned[spring_category].astype('category')
In [308]:
spring_cleaned.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/spring_cleaned.csv', index = False)
In [260]:
spring_cleaned.head()
Out[260]:
In [261]:
spring_cleaned.dtypes
Out[261]:
In [265]:
spring_booked = spring_cleaned.groupby('date').agg({"available": sum})
In [267]:
spring_booked
Out[267]:
In [269]:
spring_listed = spring_cleaned.groupby('date')['price'].count().to_frame()
In [270]:
spring_listed
Out[270]:
In [275]:
spring_booked['booked_rate'] = round(spring_booked['available'] / spring_listed['price'],2)
In [277]:
spring_booked.reset_index(level = 0, inplace = True)
In [279]:
spring_booked.rename(columns = {"date":'month','available':'num_booked'})
Out[279]:
In [382]:
spring_week_listing = spring_cleaned.groupby('weekdays')['price'].count().to_frame()
spring_week_booked = spring_cleaned.groupby('weekdays').agg({'available' : sum})
spring_week_booked['booked_rate'] = round(spring_week_booked['available'] / spring_week_listing['price'],3)
spring_week_booked.reset_index(level = 0, inplace= True)
spring_week_booked.rename(columns = {'available' : 'num_booked'})
Out[382]:
In [383]:
spring_week_booked.sort_values('booked_rate', ascending =False)
Out[383]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
Winter adding dec¶
In [401]:
print(os.getcwd())
In [400]:
os.chdir("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips")
In [402]:
winter = ['oct','nov19','dec']
calwinter = "winter_"
winter_month = {}
for i in winter:
winter_month[calwinter + i] = pd.read_csv("calendar_" + i +".csv")
In [403]:
for i in winter_month:
print(i)
In [407]:
for i in winter_month:
winter_month[i]['date'] = pd.to_datetime(winter_month[i]['date'])
winter_month[i]['weekday'] = winter_month[i]['date'].apply(lambda x : x.weekday())
winter_month[i]['weekdays'] = winter_month[i]['weekday'].apply(lambda x : weekDays[x])
In [408]:
winter_df = winter_month['winter_oct']
In [409]:
winter_keys = list(winter_month.keys())
for i in winter_month:
winter_df = pd.concat([winter_df,winter_month[i]], axis = 0)
In [410]:
winter_df.shape
Out[410]:
In [411]:
winter_df['date'] = winter_df['date'].dt.month
In [412]:
winter_df['date'].value_counts()
Out[412]:
In [413]:
winter_df = winter_df[(winter_df['date'] == 10) | (winter_df['date'] == 11) |(winter_df['date'] == 12)]
In [414]:
winter_df.shape
Out[414]:
In [415]:
winter_df.isnull().sum()
Out[415]:
Removing missing values from winter_df¶
In [416]:
winter_cleaned = winter_df.dropna()
In [417]:
winter_cleaned['price'] = winter_cleaned['price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [418]:
winter_cleaned['adjusted_price'] = winter_cleaned['adjusted_price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [298]:
winter_cleaned.dtypes
Out[298]:
f refers to the listing is booked, and t refers to the listing is available.¶
In [419]:
winter_cleaned['available'] = winter_cleaned['available'].map(dict(f=1,t=0))
In [420]:
winter_category = ['date','weekday']
winter_cleaned[winter_category] = winter_cleaned[winter_category].astype('category')
In [421]:
winter_cleaned.shape
Out[421]:
In [422]:
winter_cleaned.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/winter_cleaned_updated.csv', index = False)
In [357]:
winter_booked = winter_cleaned.groupby('date').agg({"available":sum})
In [358]:
winter_listings = winter_cleaned.groupby('date')['price'].count().to_frame()
In [314]:
winter_booked['booked_rate'] = round(winter_booked['available'] / winter_listings['price'],2)
In [316]:
winter_booked.reset_index(level=0, inplace = True)
In [317]:
winter_booked
Out[317]:
In [320]:
winter_booked = winter_booked.rename(columns = {'date':'month','available':'num_booked'})
In [359]:
winter_week_booked = winter_cleaned.groupby('weekdays').agg({'available' : sum})
In [360]:
winter_week_listing = winter_cleaned.groupby('weekdays')['price'].count().to_frame()
In [361]:
winter_week_booked['booked_rate'] = round( winter_week_booked['available'] / winter_week_listing['price'],3)
In [363]:
winter_week_booked.reset_index(level = 0, inplace=True)
In [365]:
winter_week_booked = winter_week_booked.rename(columns = {'available':'booked'})
In [366]:
winter_week_booked.sort_values('booked_rate', ascending = False)
Out[366]:
In [334]:
winter_df.shape
Out[334]:
In [446]:
summer_cleaned.shape
In [337]:
spring_cleaned.shape
Out[337]:
In [321]:
winter_booked
Out[321]:
In [445]:
summer_booked
In [332]:
spring_booked
Out[332]:
In [ ]:
#study summer price treand and booked rate first.
In [447]:
calsummer ='summer_'
months = {}
for i in summer:
months[calsummer + i] = pd.read_csv("calendar_" + i + ".csv")
In [405]:
weekDays = ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
In [182]:
weekDays = ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
for i in calendars:
calendars[i]['date'] = pd.to_datetime(calendars[i]['date'])
calendars[i]['weekday'] = calendars[i]['date'].apply(lambda x : x.weekday())
calendars[i]['weekdays'] = calendars[i]['weekday'].apply(lambda x : weekDays[x])
In [448]:
weekDays = ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
for i in months:
months[i]['date'] = pd.to_datetime(months[i]['date'])
months[i]['weekday'] = months[i]['date'].apply(lambda x : x.weekday())
months[i]['weekdays'] = months[i]['weekday'].apply(lambda x : weekDays[x])
In [ ]:
def weekday(months):
weekDays = ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
for i in months:
months[i]['date'] = pd.to_datetime(months[i]['date'])
months[i]['weekday'] = months[i]['date'].apply(lambda x : x.weekday())
months[i]['weekdays'] = months[i]['weekday'].apply(lambda x : weekDays[x])
In [278]:
#calendars['cal_jan']['date'].dtype
Concatenate 3 summer calendar tables to one table named summer_df¶
In [449]:
months.keys()
Out[449]:
In [450]:
summer_list = list(months.keys())
summer_df = months['summer_july']
for i in summer_list:
summer_df = pd.concat([summer_df, months[i]], axis = 0 )
In [132]:
summer_df.shape
Out[132]:
In [ ]:
summer_df['date']
In [451]:
summer_df['date'] = summer_df['date'].dt.month
In [452]:
summer_df['date'].value_counts()
Out[452]:
Filter out data with only July, August, and September¶
In [453]:
summer_cleaned = summer_df[(summer_df['date'] == 7) | (summer_df['date'] == 8) | (summer_df['date'] == 9) ]
In [136]:
print("After filter out data of July, August, and September, the new dataset summer_cleaned has %s of rows." % summer_cleaned.shape[0])
check missing values in summer_df table¶
In [454]:
summer_cleaned.isnull().sum()
Out[454]:
In [455]:
summer_nona = summer_cleaned.dropna()
In [140]:
print("After dropping all rows with na values, the shape of the new summer_df is: %s" % summer_nona.shape[0])
print("The original shape is: %s" % summer_df.shape[0])
In [95]:
#duplicated_listing = summer_cleaned[summer_cleaned.duplicated()]
In [97]:
#duplicated_listing.head(50)
Convert certain columns data typers to proper one.¶
In [141]:
summer_cleaned.dtypes
Out[141]:
In [272]:
summer_cleaned[summer_cleaned['price'] != summer_cleaned['adjusted_price']].head()
Out[272]:
Convert object data type columb price and adjusted price to float data type¶
In [456]:
summer_cleaned['price'] = summer_cleaned['price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
summer_cleaned['adjusted_price'] = summer_cleaned['price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [457]:
print(os.getcwd())
In [458]:
summer_cleaned.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_2019_cleaned.csv')
Convert inte64 data type of date to category data type.¶
In [143]:
summer_cleaned.head()
Out[143]:
In [274]:
summer_cleaned.weekdays = summer_cleaned.weekdays.astype('category')
In [275]:
summer_cleaned.weekday = summer_cleaned.weekday.astype('category')
In [276]:
summer_cleaned.date = summer_cleaned.date.astype('category')
In [277]:
summer_cleaned.dtypes
Out[277]:
In [148]:
summer_cleaned.groupby('date')['price'].mean().sort_values(ascending = False)
Out[148]:
Convert available to 1 and 0. 1 refers to booked, 0 means it is available to book.¶
In [459]:
summer_cleaned.available = summer_cleaned.available.map(dict(f=1,t=0))
In [460]:
summer_cleaned.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_2019_cleaned_final.csv')
In [151]:
summer_cleaned.head()
Out[151]:
In [279]:
summer_cleaned.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_cleaned.csv', index = False)
In [461]:
listings_grouped = summer_cleaned.groupby('date')['price'].count().to_frame()
In [462]:
available_grouped['available'] /listings_grouped['price']
In [468]:
listings_grouped
Out[468]:
In [466]:
summer_booked = summer_cleaned.groupby('date').agg({'available' : sum})
In [471]:
summer_avg_price = summer_cleaned.groupby('date').agg({'price' : mean})
In [472]:
summer_avg_price
Out[472]:
In [469]:
summer_booked['book_rate'] = round(summer_booked['available'] /listings_grouped['price'],2)
In [473]:
summer_booked['avg_price'] = round(summer_avg_price.price,2)
In [475]:
summer_booked.reset_index(inplace = True)
In [476]:
summer_booked
Out[476]:
In [477]:
print(os.getcwd())
In [478]:
summer_booked.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_2019_avg_price.csv')
In [327]:
summer_booked.reset_index(level = 0,inplace = True)
In [329]:
summer_booked = booked_grouped.rename(columns = {'date':'month','available':'num_booked'})
In [482]:
summer_booked['booked_rate_2020'] = current_booked['booked_rate']
In [483]:
summer_booked
Out[483]:
In [485]:
summer_booked.to_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/summer_2019_2020.csv")
In [484]:
print(os.getcwd())
In [486]:
summer_week_booked = summer_cleaned.groupby('weekdays').agg({'available' : sum})
In [487]:
summer_week_listed = summer_cleaned.groupby('weekdays')['price'].count().to_frame()
In [488]:
summer_week_booked['booked_rate'] = round(summer_week_booked['available'] / summer_week_listed['price'],3)
In [489]:
summer_week_booked.reset_index(level = 0, inplace = True)
In [490]:
summer_week_booked = summer_week_booked.rename(columns = {'available':'num_booked'})
In [491]:
summer_week_booked
Out[491]:
In [376]:
summer_week_booked.sort_values('booked_rate',ascending = False)
Out[376]:
Concatenate 3 winter calendar tables to one table named winter_df¶
In [ ]:
winter = ['oct','nov1','nov15','nov19','dec']
In [ ]:
Listings for summer¶
In [250]:
Concatenate 12 calendar tables to one final table named calendar_df¶
In [183]:
calendars.keys()
Out[183]:
In [185]:
cal_list = list(calendars.keys())
cal_list
Out[185]:
In [189]:
calendar_df = calendars['cal_jan']
for i in cal_list[1:]:
calendar_df = pd.concat([calendar_df, calendars[i]], axis = 0)
In [282]:
#calendar_df['listing_id'].value_counts().shape
In [190]:
calendar_df.shape
Out[190]:
Checking missing values of calendar dataframe¶
In [191]:
calendar_df.isnull().sum()
Out[191]:
In [ ]:
calendar_df[['weekdays','price']].groupby('weekdays').agg(lambda x: x.mean())
In [284]:
#airbnb_nomissing['id'].value_counts().shape
1. Concatenate 12 listings tabels into one final table named airbnb_df¶
In [254]:
summer_listings = pd.concat([names['la_july'], names['la_aug'],names['la_sep']], axis=0)
In [398]:
airbnb_training.columns
In [ ]:
summer_listings.head()
In [257]:
summer_listings = summer_listings[['id', 'host_is_superhost', 'host_total_listings_count', 'zipcode',
'property_type', 'room_type', 'accommodates', 'bathrooms', 'bedrooms',
'beds', 'amenities', 'price', 'cleaning_fee', 'guests_included',
'minimum_nights', 'maximum_nights', 'instant_bookable',
'calculated_host_listings_count_entire_homes',
'calculated_host_listings_count_private_rooms',
'calculated_host_listings_count_shared_rooms']]
In [314]:
summer_merged['room_type'].value_counts()
Out[314]:
In [315]:
summer_merged.isnull().sum()
Out[315]:
In [316]:
summer_merged.shape
Out[316]:
In [318]:
summer_merged = summer_merged.dropna()
In [330]:
summer_merged['amenities'] = summer_merged['amenities'].apply(lambda x : len(x.split(",")))
In [ ]:
airbnb_subset = airbnb_partial_cleaned[airbnb_partial_cleaned.property_type
.isin(['Apartment',"House",'Condominium','Guesthouse'])]
In [331]:
summer_merged['property_type'].value_counts()
Out[331]:
In [350]:
summer_merged_subset = summer_merged[summer_merged.property_type
.isin(['Apartment',"House",'Condominium','Guesthouse'])]
In [348]:
summer_merged_drop_columns = ['price_x', 'guests_included',
'minimum_nights_x', 'maximum_nights_x', 'listing_id','adjusted_price']
In [341]:
summer_merged_subset = summer_merged_subset.drop(columns = summer_merged_drop_columns)
In [346]:
summer_merged_subset.columns
Out[346]:
In [347]:
airbnb_training.columns
Out[347]:
In [309]:
summer_cleaned.head()
Out[309]:
In [311]:
summer_merged = summer_listings.merge(summer_cleaned, left_on = 'id', right_on = 'listing_id')
In [325]:
summer_cleaned[summer_cleaned['listing_id']==109].shape
Out[325]:
In [328]:
summer_cleaned['date'].value_counts()
Out[328]:
In [324]:
summer_merged.head(100)
Out[324]:
In [ ]:
df1.merge(df2, left_on='lkey', right_on='rkey')
In [ ]:
In [279]:
keys_list = list(names.keys())
airbnb_df = names['la_jan']
for i in keys_list[1:]:
airbnb_df = pd.concat([airbnb_df, names[i]], axis = 0)
In [280]:
airbnb_df.shape
Out[280]:
In [281]:
print(os.getcwd())
In [282]:
airbnb_df.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/airbnb_df_original.csv')
Subset dataset only containing state of "CA, Ca, ca"¶
In [283]:
airbnb_df['state'].value_counts()
Out[283]:
In [284]:
airbnb_ca = airbnb_df[airbnb_df['state'].isin(['CA','Ca','ca'])]
In [287]:
print(os.getcwd())
In [288]:
airbnb_ca.to_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/airbnb_ca_only.csv")
In [285]:
print("The dataset before subsetting has %s" %airbnb_df.shape[0] + ' rows.')
print("The dateser after subsetting has %s" %airbnb_ca.shape[0] + ' rows.')
In [286]:
airbnb_ca.last_scraped.value_counts()
Out[286]:
Why I did this?¶
In [ ]:
airbnb_ca = airbnb_ca[~airbnb_ca.last_scraped.isin(['2019-11-15','2019-11-20'])]
In [219]:
airbnb_ca.shape
Out[219]:
In [274]:
airbnb_ca.head()
In [290]:
with_long_lat = airbnb_ca[columns_needed]
In [289]:
columns_needed = ['neighbourhood_cleansed','price','property_type','room_type','latitude',
'longitude']
In [291]:
with_long_lat[['neighbourhood_cleansed','property_type','room_type']].apply(pd.Series.nunique, axis = 'rows')
Out[291]:
In [369]:
with_long_lat[['neighbourhood_cleansed','property_type','room_type']] = with_long_lat[['neighbourhood_cleansed','property_type','room_type']].astype('category')
In [370]:
property_type_table = with_long_lat.property_type.value_counts().to_frame()
In [371]:
property_type_df = property_type_table.reset_index()
In [372]:
property_type_df
Out[372]:
In [373]:
with_long_lat_subset= with_long_lat[with_long_lat.property_type
.isin(['Apartment',"House",'Condominium','Guesthouse'])]
In [ ]:
In [374]:
with_long_lat_subset.loc[:,"property_type"] = with_long_lat_subset.loc[:,"property_type"].cat.remove_unused_categories()
In [375]:
with_long_lat_nonas = with_long_lat_subset.dropna()
In [376]:
with_long_lat_nonas.shape
Out[376]:
In [378]:
with_long_lat_nonas.price = with_long_lat_nonas.price.astype(str).str.replace("$","").str.replace(",","").astype(float)
In [379]:
with_long_lat_nonas.head()
Out[379]:
In [380]:
print(os.getcwd())
In [381]:
with_long_lat_nonas.to_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/with_long_lat_nonas.csv")
In [299]:
print(os.getcwd())
In [300]:
property_type_df.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/property_type_df.csv')
In [ ]:
In [294]:
# some of these features might be having missing values, and some of them might be not releveant to
#train machine learning model. Therefore, datacleaning is very necessary to prepare the data.
#The strategy used for cleaning the data is as follows:
#1. Drop columns that are not relevant to our objective. For example: Experiences_offer, URL
#2. Find missing values for each column after being cleaned from step1.
#3. Question here. what if some columns have so many missing values? which is more than 30% of the entire data?
#4. Covert columns to their correct data type.
#5.
Drop Columns¶
In [220]:
#check how data types are distributed.
airbnb_ca.dtypes.value_counts()
Out[220]:
In [221]:
airbnb_ca.select_dtypes(include = ['object']).columns
Out[221]:
In [222]:
drop_object_list = ['listing_url','last_scraped','name','summary','description','experiences_offered','picture_url',
'host_url','host_name', 'host_since', 'host_location', 'host_about',
'host_thumbnail_url', 'host_picture_url', 'host_neighbourhood',
'street', 'neighbourhood','city', 'state','neighbourhood_group_cleansed','market','smart_location',
'country_code', 'country','calendar_updated','calendar_last_scraped',
'first_review', 'last_review',"jurisdiction_names",'is_business_travel_ready',
'interaction','access','transit','house_rules','cancellation_policy',
'host_has_profile_pic','host_verifications','require_guest_profile_picture',
'require_guest_phone_verification','bed_type','security_deposit','requires_license',
'host_has_profile_pic',"host_identity_verified","extra_people",
'has_availability','is_location_exact','neighborhood_overview','zipcode','instant_bookable'
]
In [ ]:
## export this drop object list as csv and send it to DD.
Drop irrelevant float64 types¶
In [223]:
airbnb_ca.select_dtypes(include=["float64"]).columns
Out[223]:
In [ ]:
In [224]:
#The reason that columns of latitude and longitude is not being dropped is they may be helpful when creating
# visualizations.
drop_float_list = ['thumbnail_url', 'medium_url', 'xl_picture_url',
'minimum_nights_avg_ntm', 'maximum_nights_avg_ntm',
'host_listings_count','latitude',
'longitude','host_total_listings_count'
]
Drop irrelevant int64 types¶
In [55]:
#does availability_30 and etc mean how many days the listing would be available
#within a year?
# calculated_host_listings_count. Do we need to keep this column? one-hot encoding is ready.
In [56]:
# what d
In [225]:
airbnb_ca[['guests_included','room_type', 'calculated_host_listings_count_entire_homes',
'calculated_host_listings_count_private_rooms','calculated_host_listings_count_shared_rooms']].head()
Out[225]:
In [226]:
airbnb_ca.select_dtypes(include=["int64"]).columns
Out[226]:
In [227]:
drop_int_list = ['host_id','scrape_id','minimum_minimum_nights','maximum_minimum_nights',
'minimum_maximum_nights','maximum_maximum_nights','calculated_host_listings_count',
"number_of_reviews","number_of_reviews_ltm",'availability_30', 'availability_60',
'availability_90', 'availability_365','calculated_host_listings_count',
'calculated_host_listings_count_entire_homes',
'calculated_host_listings_count_private_rooms',
'calculated_host_listings_count_shared_rooms','maximum_nights','guests_included']
In [290]:
#airbnb_ca[['calculated_host_listings_count','calculated_host_listings_count_entire_homes',
#'calculated_host_listings_count_private_rooms','calculated_host_listings_count_shared_rooms']].head()
In [228]:
#combine all of the columns which are supposed to be dropped.
drop_columns = drop_object_list + drop_float_list + drop_int_list
print(drop_columns)
In [229]:
print("Dropping %s columns" %len(drop_columns))
airbnb_ca_cleaned = airbnb_ca.drop(columns = drop_columns)
print("Shape fo the dataset after dropping : " , airbnb_ca_cleaned.shape)
missing value percentage for each column statistics¶
In [230]:
missing_stats = airbnb_ca_cleaned.isnull().sum().to_frame()
In [231]:
missing_stats.shape
Out[231]:
In [232]:
missing_stats.columns=["missing_num"]
In [233]:
missing_stats["missing_pctg"] = round(100* missing_stats["missing_num"] / airbnb_ca_cleaned.shape[0])
In [234]:
missing_stats.sort_values(by = "missing_num", ascending =False, inplace = True)
In [235]:
display(missing_stats)
In [236]:
drop_missing_values = list(missing_stats[missing_stats['missing_pctg'] >= 19].index)
print(drop_missing_values)
In [237]:
airbnb_partial_cleaned = airbnb_ca_cleaned.drop(columns= drop_missing_values)
In [238]:
airbnb_partial_cleaned.shape
Out[238]:
Convert data type¶
1.some columns with object type should be changed to categorical type so as to run one-hot encoding when training the model
In [239]:
airbnb_partial_cleaned['price'] = airbnb_partial_cleaned['price'].astype(str).str.replace("$","").str.replace(",","").astype(float)
airbnb_partial_cleaned['cleaning_fee'] = airbnb_partial_cleaned['cleaning_fee'].astype(str).str.replace("$","").str.replace(",","").astype(float)
In [240]:
airbnb_partial_cleaned.columns
Out[240]:
In [241]:
# which columns should be changed to categorical?
airbnb_partial_cleaned.select_dtypes(include = "object").head()
Out[241]:
In [242]:
airbnb_partial_cleaned.select_dtypes(include = 'object').columns
Out[242]:
In [162]:
categorical_cols = ['host_is_superhost', 'property_type','room_type',
'neighbourhood_cleansed'
]
In [244]:
airbnb_partial_cleaned[categorical_cols].dtypes
Out[244]:
In [245]:
airbnb_partial_cleaned[categorical_cols] = airbnb_partial_cleaned[categorical_cols].apply(lambda x : x.astype('category'),
axis = 'rows')
In [246]:
airbnb_partial_cleaned[categorical_cols].dtypes
Out[246]:
In [247]:
airbnb_partial_cleaned.host_is_superhost = airbnb_partial_cleaned.host_is_superhost.map(dict(f=0,t=1))
In [ ]:
airbnb_partial_cleaned.host_is_superhost = airbnb_partial_cleaned.host_is_superhost.map(dict(f=0,t=1))
In [405]:
airbnb_partial_cleaned.shape
Out[405]:
2. Filter data¶
1.Find out how many unqiue values each of these columns have.¶
In [248]:
num_unique_values = airbnb_partial_cleaned[categorical_cols].apply(pd.Series.nunique, axis = 'rows')
num_unique_values
Out[248]:
In [204]:
num_unique_values.plot(kind = 'bar')
plt.xlabel('labels')
plt.ylabel('Number of unique values')
plt.show()
In [106]:
#airbnb_partial_cleaned.to_csv("partial_cleaned_listings_without_subsetting.csv", index = False)
In [249]:
airbnb_subset = airbnb_partial_cleaned[airbnb_partial_cleaned.property_type
.isin(['Apartment',"House",'Condominium','Guesthouse'])]
In [ ]:
In [250]:
airbnb_subset.shape
Out[250]:
2. Drop those unused categories of property types.¶
In [251]:
airbnb_subset.loc[:,"property_type"] = airbnb_subset.loc[:,"property_type"].cat.remove_unused_categories()
In [252]:
airbnb_subset['property_type'].value_counts()
Out[252]:
3. Convert amenities to the number of elements included in amenities for each observation.¶
In [253]:
airbnb_subset['amenities'] = airbnb_subset['amenities'].apply(lambda x : len(x.split(",")))
4. Missing Values Statistics for the subset¶
In [254]:
subset_missing = airbnb_subset.isnull().sum().to_frame()
subset_missing.columns = ["missing_values"]
subset_missing['percentage'] =(100 * subset_missing['missing_values'] / airbnb_subset.shape[0])
In [255]:
subset_missing
Out[255]:
Check if columns contain host are having missing values for same observations.¶
In [212]:
cols_missing_values = list(subset_missing[subset_missing.missing_values > 0].index)
airbnb_subset_missing_values = airbnb_subset[cols_missing_values]
In [213]:
host_cols = list(airbnb_subset_missing_values.columns[airbnb_subset_missing_values.columns.str.contains('host')])
In [203]:
host_cols
Out[203]:
In [204]:
airbnb_subset_missing_values[host_cols][airbnb_subset_missing_values.host_is_superhost.isnull()].shape
Out[204]:
The result above proves that those three host_cols have same missing values for the same observations.¶
Delete all of those missing values and to see how many observations left.¶
In [256]:
airbnb_nomissing = airbnb_subset.dropna()
In [257]:
airbnb_nomissing.head()
Out[257]:
In [258]:
airbnb_nomissing.columns
Out[258]:
In [259]:
print("After dropping all of missing values for each column, the dataset named airbnb_nomissing \n has %s" % airbnb_nomissing.shape[0]
+ " rows."
)
In [ ]:
a
3. One hot encoding categorical variables.¶
Keep latitude and longitude columns for creating visualizations. So assign a new dataset without latitude and longitude to airbnb_training.¶
In [410]:
#airbnb_training = airbnb_nomissing.drop(['latitude','longitude'], axis = 'columns')
In [51]:
#if "latitude" in airbnb_training.columns:
# print('yes')
#else:
# print('no')
In [260]:
airbnb_la = airbnb_nomissing
In [92]:
print(os.getcwd())
In [262]:
airbnb_la.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/airbnb_la.csv', index = False)
In [149]:
print(os.getcwd())
In [21]:
airbnb_la = pd.read_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/airbnb_la.csv")
In [22]:
categorical_cols = ['host_is_superhost', 'property_type','room_type',
'neighbourhood_cleansed'
]
In [20]:
airbnb_la.dtypes
Out[20]:
In [231]:
airbnb_la.property_type.value_counts()
Out[231]:
In [235]:
airbnb_la[airbnb_la['room_type'] == 'Hotel room']
Out[235]:
In [237]:
airbnb_la_no_hotel =airbnb_la.drop([277461,277507])
In [234]:
airbnb_la.room_type.head()
Out[234]:
In [256]:
categorical_cols
Out[256]:
In [257]:
airbnb_la_no_hotel[categorical_cols] = airbnb_la_no_hotel[categorical_cols].astype('category')
In [260]:
no_hotel_onehot = pd.get_dummies(airbnb_la_no_hotel[categorical_cols])
In [262]:
airbnb_la_no_hotel.dtypes
Out[262]:
In [240]:
no_hotel_onehot.shape
Out[240]:
In [263]:
airbnb_nohotel_dummies = pd.concat([no_hotel_onehot, airbnb_la_no_hotel], axis = 'columns').drop(categorical_cols,
axis = 'columns')
In [266]:
airbnb_nohotel_dummies = airbnb_nohotel_dummies.drop(['id',axis = 1)
In [265]:
airbnb_nohotel_dummies.head()
Out[265]:
In [267]:
no_hotel_price = airbnb_la_no_hotel['price']
In [268]:
no_hotel_ols = stat.OLS(no_hotel_price, airbnb_nohotel_dummies)
no_hotel_ols_result = no_hotel_ols.fit()
In [269]:
print(no_hotel_ols_result.summary())
In [23]:
airbnb_la[categorical_cols] = airbnb_la[categorical_cols].astype('category')
In [24]:
category_one_hot = pd.get_dummies(airbnb_la[categorical_cols])
In [25]:
category_one_hot.shape
Out[25]:
In [165]:
category_one_hot.head()
Out[165]:
In [26]:
airbnb_with_dummies = pd.concat([category_one_hot, airbnb_la], axis = 'columns').drop(categorical_cols,
axis = 'columns')
In [27]:
airbnb_with_dummies.shape
Out[27]:
In [28]:
print(os.getcwd())
Up to this point, the data is ready to train the model.¶
In [29]:
airbnb_with_dummies.to_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/airbnb_with_dummies.csv", index = False)
In [363]:
airbnb_with_dummies.head()
Out[363]:
In [ ]:
### adding month columns
###correlation matrix
### atrributes affect the price.
In [178]:
len(airbnb_la.columns)
Out[178]:
In [404]:
airbnb_la.columns
Out[404]:
In [229]:
airbnb_la['room_type'].value_counts()
Out[229]:
In [508]:
airbnb_la.groupby('room_type').agg({'price':mean})
Out[508]:
In [303]:
airbnb_corr = airbnb_la.drop(['id','price'],axis =1)
In [180]:
import seaborn as sns
In [304]:
corr = airbnb_corr.corr()
In [318]:
ax = sns.heatmap(
corr,
vmin=-1, vmax=1, center=0,
cmap=sns.diverging_palette(20, 220, n=200),
square=True
)
ax.set_xticklabels(
ax.get_xticklabels(),
rotation=45,
horizontalalignment='right'
);
In [ ]:
, mask=np.zeros_like(corr, dtype=np.bool)
sns.heatmap(corr, annot = True, cmap=sns.diverging_palette(230, 20, as_cmap=True),
square=True, ax=ax)
In [311]:
sns.heatmap(corr, annot=True)
plot.show()
In [337]:
print(os.getcwd())
In [367]:
f, ax = plt.subplots(figsize=(9, 8))
corr = airbnb_corr.corr()
sns.heatmap(corr, annot = True, annot_kws={"size":13}, fmt=".2f",cmap=sns.diverging_palette(220, 10, as_cmap=True))
ax.set_ylim(np.array([-0.5, -.5])+ax.get_ylim())
plt.xticks(fontsize=13, rotation=45)
plt.yticks(fontsize=13)
plt.savefig('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/correlation_matrix2.jpeg',bbox_inches='tight')
In [321]:
sns.heatmap(airbnb_corr.corr(),mask=np.zeros_like(corr, dtype=np.bool), cmap=sns.diverging_palette(220, 10, as_cmap=True),
square=True, ax=ax)
Out[321]:
In [ ]:
In [17]:
airbnb_la = pd.read_csv("airbnb_la.csv")
In [68]:
# Concatenate two tables together.
airbnb_with_dummies = pd.concat([category_one_hot, airbnb_la], axis = 'columns').drop(categorical_cols,
axis = 'columns')
In [499]:
airbnb_la.to_csv(r'/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/airbnb_la.csv', index = False)
In [497]:
print(os.getcwd())
In [69]:
airbnb_with_dummies.shape
Out[69]:
In [179]:
airbnb_with_dummies.columns
Out[179]:
4. Applying 10 fold cross validations on dataset.¶
Linear Regression¶
In [282]:
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))
In [70]:
airbnb_with_dummies.head()
Out[70]:
In [204]:
airbnb_with_dummies.head()
Out[204]:
The amenities are retained for model trainig.¶
In [88]:
airbnb_extra_columns = ['price','id','accommodates']
drop_extra_columns = airbnb_with_dummies.drop(airbnb_extra_columns,axis = 1)
In [ ]:
In [ ]:
### check the number of hotal rooms
In [89]:
drop_extra_columns.head()
Out[89]:
In [ ]:
airbnb_no_beds_columns = ['price','id','beds',]
this is x_train without scaling.¶
In [90]:
noscale_x_train = drop_extra_columns
In [86]:
if 'accommodates'.isin([noscale_x_train.columns]):
print('yes')
In [33]:
print(os.getcwd())
In [34]:
noscale_x_train.to_csv("/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/noscale_x_train_without_price_id_accommodates.csv")
In [74]:
noscale_x_train.head()
Out[74]:
In [285]:
x_train= drop_extra_columns
In [286]:
x_train.head()
Out[286]:
In [220]:
y_train= airbnb_with_dummies['price']
In [279]:
import statsmodels.api as stat
In [281]:
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))
In [294]:
noscale_x_train.head()
Out[294]:
In [322]:
noscale_x_train.columns
Out[322]:
In [ ]:
X = stat.add_constant(X, prepend=False)
In [367]:
noscale_x_train.head()
Out[367]:
In [364]:
noscale_x_train = stat.add_constant(noscale_x_train, prepend = False)
In [382]:
noscale_x_train_1 = noscale_x_train.drop(['host_is_superhost_0','property_type_Apartment','room_type_Hotel room'], axis = 1)
In [383]:
ols2 = stat.OLS(y_train, noscale_x_train_1)
ols2_result = ols2.fit()
In [384]:
print(ols2_result.summary())
In [ ]:
In [92]:
ols =stat.OLS(y_train, noscale_x_train)
ols_result =ols.fit()
Superhost has negative effect¶
In [ ]:
### Email Jinan to ask about property type
In [ ]:
### Drop the hotel room types as it only has two listings.
In [1]:
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))
In [93]:
print(ols_result.summary())
In [ ]:
In [ ]:
d = {'col1': [1, 2], 'col2': [3, 4]}
In [96]:
factors = {'features':['room_type_Entire home/apt','room_type_Private room','room_type_Shared room','neighbourhood_cleansed_Avalon',
'Bel-Air','Beverly Crest','Beverly Hills','Century City','Encino','Hasley Canyon','Hollywood Hills West','Malibu',
'Pacific Palisades','Unincorporated Catalina Island','Unincorporated Santa Monica Mountains','Unincorporated Santa Susana Mountains',
'Vernon','Westlake Village','bathrooms','bedrooms','beds','cleaning_fee','minimum_nights','amenities'],
'coeffits':[77.4344,88.3273, 5.6587,139.7511, 573.3431 ,285.5859, 82.3291,286.9593, 84.4890,84.4743, 226.2936, 609.6742,
100.0329,404.3530,245.2676, 87.1175, 84.8923, 120.2296,139.7979,21.7480, -9.4181, 1.2046, -0.5185, -0.6230]}
In [213]:
factors_df = pd.DataFrame(data=factors)
In [214]:
factors_df.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/factors.csv')
In [101]:
print(os.getcwd('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/factors.csv'))
In [191]:
coef_df= pd.DataFrame(ols_result.summary().tables[1].data)
coef_df.columns = coef_df.iloc[0]
coef_df = coef_df.drop(0)
In [193]:
coef_df = coef_df.set_index(coef_df.columns[0])
In [195]:
coef_df = coef_df.astype(float)
In [196]:
errors = coef_df['coef'] - coef_df['[0.025']
In [197]:
coef_df['errors'] = errors
coef_df = coef_df.sort_values(by=['coef'])
variables = list(coef_df.index.values)
In [199]:
coef_df['variables'] = variables
In [201]:
coef_df
Out[201]:
Neighbourhood only¶
In [202]:
without_neighbours = ['room_type_Entire home/apt','room_type_Private room','room_type_Shared room',
'room_type_Hotel room', 'bathrooms','bedrooms','beds','cleaning_fee','minimum_nights',
'amenities','property_type_Apartment','property_type_Condominium','property_type_Guesthouse',
'property_type_House']
In [206]:
with_neighbours_coeffcis = coef_df.drop(without_neighbours)
In [207]:
with_neighbours_coeffcis
Out[207]:
In [208]:
fig, ax = plt.subplots(figsize=(15, 10))
with_neighbours_coeffcis.plot(x='variables', y='coef', kind='bar',
ax=ax, color='none', fontsize=22,
ecolor='steelblue',capsize=0,
yerr='errors', legend=False)
plt.title('Coefficients of Features w/ 95% Confidence Intervals',fontsize=30)
ax.set_ylabel('Coefficients',fontsize=22)
ax.set_xlabel('',fontsize=22)
ax.scatter(x=pd.np.arange(coef_df.shape[0]),
marker='o', s=80,
y=coef_df['coef'], color='steelblue')
# Line to define zero on the y-axis
ax.axhline(y=0, linestyle='--', color='red', linewidth=1)
Out[208]:
In [ ]:
In [210]:
without_neighbours_df = coef_df.loc[without_neighbours]
In [211]:
fig, ax = plt.subplots(figsize=(15, 10))
without_neighbours_df.plot(x='variables', y='coef', kind='bar',
ax=ax, color='none', fontsize=22,
ecolor='steelblue',capsize=0,
yerr='errors', legend=False)
plt.title('Coefficients of Features w/ 95% Confidence Intervals',fontsize=30)
ax.set_ylabel('Coefficients',fontsize=22)
ax.set_xlabel('',fontsize=22)
ax.scatter(x=pd.np.arange(coef_df.shape[0]),
marker='o', s=80,
y=coef_df['coef'], color='steelblue')
# Line to define zero on the y-axis
ax.axhline(y=0, linestyle='--', color='red', linewidth=1)
Out[211]:
In [198]:
coef_df
Out[198]:
In [ ]:
In [181]:
def coefplot(results):
coef_df= pd.DataFrame(ols_result.summary().tables[1].data)
coef_df.columns = coef_df.iloc[0]
coef_df = coef_df.drop(0)
coef_df = coef_df.set_index(coef_df.columns[0])
coef_df = coef_df.astype(float)
errors = coef_df['coef'] - coef_df['[0.025']
coef_df['errors'] = errors
coef_df = coef_df.sort_values(by=['coef'])
variables = list(coef_df.index.values)
coef_df['variables'] = variables
sns.set_context("poster")
fig, ax = plt.subplots(figsize=(15, 10))
coef_df.plot(x='variables', y='coef', kind='bar',
ax=ax, color='none', fontsize=22,
ecolor='steelblue',capsize=0,
yerr='errors', legend=False)
plt.title('Coefficients of Features w/ 95% Confidence Intervals',fontsize=30)
ax.set_ylabel('Coefficients',fontsize=22)
ax.set_xlabel('',fontsize=22)
ax.scatter(x=pd.np.arange(coef_df.shape[0]),
marker='o', s=80,
y=coef_df['coef'], color='steelblue')
# Line to define zero on the y-axis
ax.axhline(y=0, linestyle='--', color='red', linewidth=1)
return plt.show()
In [182]:
coefplot(ols_result)
Seperate Coefficients¶
In [ ]:
In [177]:
pd.DataFrame(ols_result.summary().tables[1].data)
Out[177]:
In [ ]:
In [102]:
factors_df.to_csv('/Users/sitebai/Desktop/UCI/fall/bana212da/listing_zips/factors_df.csv')
In [98]:
factors.to_frame()
In [ ]:
neighbourhood_cleansed_Avalon : 135.6014
In [83]:
ols_result.summary()[0]
Linear Regression with predict¶
In [11]:
from sklearn import metrics
Split the dataset to 80% of training and 20% of testing¶
In [36]:
y_train= airbnb_with_dummies['price']
In [ ]:
no_sale_x_train
In [37]:
X_train, X_test, Y_train, Y_test = train_test_split(noscale_x_train, y_train, test_size = 0.2, random_state = 462)
In [328]:
airbnb_linreg = LinearRegression()
In [329]:
airbnb_linreg.fit(X_train, Y_train)
Out[329]:
In [353]:
airbnb_linreg.score(X_train, Y_train)
Out[353]:
In [361]:
airbnb_linreg.score(X_test,Y_test )
Out[361]:
In [330]:
airbnb_y_predict = airbnb_linreg.predict(X_test)
In [333]:
print(metrics.mean_squared_error(Y_test,airbnb_y_predict))
In [270]:
np.sqrt(71950)
Out[270]:
In [162]:
#please take the sqrt of mean
import math
math.sqrt(71950)
Out[162]:
In [ ]:
sirbnb_y_predict.score()
In [ ]:
airbnb_dum
Cross_validation linear regression¶
In [58]:
noscale_x_train.head()
Out[58]:
In [56]:
y_train.shape
Out[56]:
In [75]:
airbnb_linear = LinearRegression()
cv_linear = cross_val_score(airbnb_linear, noscale_x_train, y_train, scoring ='neg_mean_squared_error', cv = 10 )
In [80]:
mean(np.sqrt(absolute(cv_linear)))
Out[80]:
In [67]:
noscale_x_train.head()
Out[67]:
In [61]:
square_root_mse = np.sqrt(-cv_linear)
square_root_mse.mean()
Out[61]:
In [63]:
airbnb_linear_regression = LinearRegression()
airbnb_linear_mse = cross_val_score(airbnb_linear_regression , noscale_x_train, y_train, scoring ='neg_mean_squared_error', cv =10)
In [65]:
airbnb_linear_mse
Out[65]:
In [51]:
mean(absolute(airbnb_linear_mse))
Out[51]:
In [66]:
mean(np.sqrt(absolute(airbnb_linear_mse )))
Out[66]:
In [272]:
np.sqrt(-airbnb_linear_mse).mean()
Out[272]:
In [ ]:
x_train, x_test, y_train, y_test = train_test_split(x,y, test_size = 0.2, random_state = 462)
In [64]:
lin_reg = LinearRegression()
In [104]:
y = airbnb_with_dummies['price']
In [105]:
x = airbnb_with_dummies.drop('price',axis = 1)
In [67]:
scores = cross_val_score(lin_reg, x, y,scoring = "neg_mean_squared_error", cv=10)
In [68]:
#no need to sqrt of mse(double check)
lin_rmse_scores = np.sqrt(-scores)
In [69]:
def display_scores(scores):
print("Scores", scores)
print("Mean", scores.mean())
print("Standard Deviation:", scores.std())
In [70]:
display_scores(lin_rmse_scores)
Applying Lasso Regression and compare to Linear Regression shown above.¶
In [8]:
from numpy import mean
from numpy import std
from numpy import absolute
from pandas import read_csv
from sklearn.model_selection import cross_val_score
from sklearn.model_selection import RepeatedKFold
from sklearn.linear_model import Lasso
from numpy import arange
In [107]:
airbnb_lasso1 = Lasso(alpha=1.0)
In [243]:
model = Lasso(alpha=1.0)
In [262]:
model_2 = Lasso(alpha=0.0)
In [ ]:
model_2_results = absolute(scores)
print('Mean MAE: %.3f (%.3f)' % (mean(model_2_results), std(model_2_results)))
In [256]:
scores = cross_val_score(model, x_train, y_train, scoring='neg_mean_squared_error', cv=10, n_jobs=-1)
In [246]:
positive_scores = absolute(scores)
print('Mean MAE: %.3f (%.3f)' % (mean(positive_scores), std(positive_scores)))
In [260]:
lasso_cv = LassoCV(alphas=arange(0, 1, 0.01), cv=10, n_jobs=-1)
lasso_cv.fit(x_train, y_train)
Out[260]:
In [261]:
print('alpha: %f' % lasso_cv.alpha_)
In [257]:
after_scaling = absolute(scores)
print('Mean MAE: %.3f (%.3f)' % (mean(after_scaling), std(after_scaling)))
In [247]:
x_train.head()
Out[247]:
In [254]:
x_train[['bathrooms','bedrooms','beds','cleaning_fee','minimum_nights']] = scaler.fit_transform(x_train[['bathrooms','bedrooms','beds','cleaning_fee','minimum_nights']])
In [255]:
x_train.head()
Out[255]:
In [251]:
#Standardize dataset first
scaler = StandardScaler()
scaler.transform()
In [242]:
airbnb_lasso1 = LassoCV(normalize = True, cv = 10).fit(x,y)
importance = np.abs(airbnb_lasso1.coef_)
In [347]:
#importance
In [240]:
from sklearn import linear_model
airbnb_lasso3 = linear_model.Lasso(alpha = 0.1)
In [ ]:
In [237]:
airbnb_lasso2 = Lasso()
airbnb_lasso2.fit(dlx_train, dly_train)
train_score = lasso.score(dlx_test, dly_test)
#airbnb_lasso2 = Lasso(alpha = 0.1)
#cv = cross_val_score(airbnb_lasso2, x,y ,scoring='neg_mean_absolute_error', cv =10)
In [10]:
#cv = absolute(scores)
X_train, X_test, Y_train, Y_test = train_test_split(noscale_x_train, y_train, test_size = 0.2, random_state = 462)
In [169]:
aribnb_lasso2 = Lasso(alpha = 0.01)
aribnb_lasso2.fit(X_train, Y_train)
train_score = aribnb_lasso2.score(X_train, Y_train)
test_score = aribnb_lasso2.score(X_test, Y_test)
In [176]:
importance = np.abs(aribnb_lasso2.coef_)
len(importance[importance != 0])
Out[176]:
In [ ]:
281
In [ ]:
# loop alpha 0.001,0.02, 0.5
#it'a suppoed to be lower than linear regression
In [355]:
print(train_score)
print(test_score)
In [356]:
airbnb_yhat = aribnb_lasso2.predict(X_test)
In [39]:
from sklearn.metrics import mean_squared_error
In [359]:
mean_squared_error(Y_test, airbnb_yhat)
Out[359]:
In [271]:
np.sqrt(71963)
Out[271]:
In [14]:
X_train, X_test, Y_train, Y_test = train_test_split(noscale_x_train, y_train, test_size = 0.2, random_state = 462)
In [40]:
alphas = [0.01, 0.001, 0.002]
lasso_scores = {}
lasso_mses= []
for i in alphas:
lasso_model = Lasso(alpha = i)
lasso_model.fit(X_train, Y_train)
train_score = lasso_model.score(X_train, Y_train)
test_score = lasso_model.score(X_test, Y_test)
lasso_scores[i] = (train_score, test_score)
y_hat = lasso_model.predict(X_test)
lasso_mses.append(mean_squared_error(Y_test,y_hat))
In [165]:
lasso_scores = {}
lasso_mses= []
for i in range(1, 51):
j = 0.001 * i
lasso_model = Lasso(alpha = j)
lasso_model.fit(X_train, Y_train)
train_score = lasso_model.score(X_train, Y_train)
test_score = lasso_model.score(X_test, Y_test)
lasso_scores[i] = (train_score, test_score)
y_hat = lasso_model.predict(X_test)
lasso_mses.append(mean_squared_error(Y_test,y_hat))
In [ ]:
In [166]:
lasso_scores
Out[166]:
In [167]:
lasso_mses
Out[167]:
In [503]:
np.sqrt(71988)
Out[503]:
In [43]:
alphas = [0.001, 0.02, 1]
lasso_scores = {}
lasso_mses= []
for i in alphas:
lasso_model = Lasso(alpha = i)
lasso_model.fit(X_train, Y_train)
train_score = lasso_model.score(X_train, Y_train)
test_score = lasso_model.score(X_test, Y_test)
lasso_scores[i] = (train_score, test_score)
y_hat = lasso_model.predict(X_test)
lasso_mses.append(mean_squared_error(Y_test,y_hat))
In [44]:
lasso_scores
Out[44]:
In [45]:
lasso_mses
Out[45]:
Deep learning model¶
In [304]:
#!pip install tensorflow
import tensorflow as tf
In [227]:
#!pip install Keras
In [230]:
import keras
from keras.layers import Dense
from keras.models import Sequential
In [194]:
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense, Activation
from tensorflow.keras.optimizers import Adam
In [ ]:
from sklearn.model_selection import tranin_test_split
In [185]:
dlx_train, dlx_test, dly_train, dly_test = train_test_split(x,y, test_size = 0.2, random_state = 462)
In [249]:
from sklearn.preprocessing import StandardScaler
In [141]:
from sklearn.model_selection import StratifiedKFold
In [ ]:
In [200]:
k_fold = StratifiedKFold(n_splits=10, shuffle=True)
In [184]:
len(x.columns)
Out[184]:
In [198]:
for train, test in k_fold.split(x, y):
print (len(train))
In [210]:
x.head()
Out[210]:
In [211]:
y.head()
Out[211]:
In [ ]:
In [222]:
#Standardize dataset first
scaler = StandardScaler()
dlx_train = scaler.fit_transform(dlx_train.astype(np.float))
In [224]:
dlx_test = scaler.transform(dlx_test.astype(np.float))
In [ ]:
#besides dense, what else could be used here?
#change the input number
#dropout?
In [302]:
x.shape
Out[302]:
In [222]:
n_cols = x_train.shape[1]
In [223]:
n_cols
Out[223]:
In [235]:
airbnb_deep_model = Sequential()
airbnb_deep_model.add(Dense(200, activation = 'relu', input_shape = (n_cols,)))
airbnb_deep_model.add(Dense(200, activation = 'relu'))
airbnb_deep_model.add(Dense(200, activation = 'relu'))
airbnb_deep_model.add(Dense(200, activation = 'relu'))
airbnb_deep_model.add(Dense(1))
In [236]:
airbnb_deep_model.compile(optimizer = 'Adam', loss ='mean_squared_error')
let's compare this outcome with Lasso¶
In [240]:
#batch_size double check
airbnb_fit= airbnb_deep_model.fit(x,y,validation_split = 0.2)
#airbnb_deep_model.summary()
In [273]:
print(np.sqrt(63986))
print(np.sqrt(72638))
In [ ]:
# predict ()
In [238]:
mse_score = airbnb_deep_model.evaluate(x,y)
In [239]:
mse_score
Out[239]:
Outliers¶
In [335]:
airbnb_la.price.describe()
Out[335]:
In [345]:
airbnb_la[airbnb_la['price'] > 1.5* iqr + 189].shape
Out[345]:
In [377]:
airbnb_la[airbnb_la['price'] < 1.5* iqr -75].shape
Out[377]:
In [346]:
airbnb_la.shape
Out[346]:
In [344]:
q75, q25 = np.percentile(airbnb_la['price'], [75 ,25])
iqr = q75 - q25
In [ ]:
In [ ]:
import seaborn as sns
sns.set_theme(style="whitegrid")
tips = sns.load_dataset("tips")
ax = sns.boxplot(x=tips["total_bill"])
In [338]:
import seaborn as sns
In [339]:
sns.set_theme(style="whitegrid")
In [341]:
airbnb_la.head()
Out[341]:
In [342]:
box_plot = sns.boxplot(x = airbnb_la.room_type, y = airbnb_la.price)
In [ ]:
Visualizations¶
In [500]:
airbnb_la.head()
Out[500]:
In [ ]:
In [147]:
# what property has the highest average price?
airbnb_nomissing[['property_type','price']].groupby('property_type').agg(lambda x : x.mean())
Out[147]:
In [148]:
airbnb_nomissing[['room_type','price']].groupby('room_type').agg(lambda x : x.mean())
Out[148]:
The proportion of super host for each property type and room type¶
In [245]:
import matplotlib.pyplot as plt
import seaborn as sns
In [351]:
property_superhost = airbnb_nomissing[['property_type','host_is_superhost']].groupby('property_type').agg(lambda x : len(x == 't') )
In [436]:
property_superhost
Out[436]:
In [437]:
airbnb_nomissing[['property_type','price']].groupby('property_type').agg(lambda x : x.mean())
Out[437]:
In [ ]:
##line graph
In [ ]:
##
In [ ]:
In [352]:
room_superhost = airbnb_nomissing[['room_type','host_is_superhost']].groupby('room_type').agg(lambda x : len(x == 't') )
In [354]:
property_room_superhost =airbnb_nomissing.groupby(['property_type','room_type'])['host_is_superhost'].agg(lambda x : len(x == 't')).to_frame()
In [138]:
airbnb_with_dummies.select_dtypes(include = 'object').columns
Out[138]:
In [397]:
airbnb_training.head()
In [396]:
neighbour_property = airbnb_training.groupby('neighbourhood_cleansed')['property_type'].count().sort_values(ascending = False).to_frame()
In [216]:
neighbour_property = airbnb_la.groupby('neighbourhood_cleansed').agg({'price':mean})
In [219]:
neighbour_property = neighbour_property.reset_index()
In [224]:
sorted_average_price = neighbour_property.sort_values(by = 'price', ascending = False)
In [225]:
sorted_average_price
Out[225]:
In [395]:
neighbour_property = neighbour_property.reset_index()
neighbour_listings = neighbour_property.rename(columns = {'property_type':'num_listings'})
In [494]:
airbnb_training.head()
Out[494]:
In [520]:
neighbour_property.head()
Out[520]:
In [223]:
import folium
In [505]:
gjson2 = r'https://apps.gis.ucla.edu/geodata/dataset/93d71e41-6196-4ecb-9ddd-15f1a4a7630c/resource/6cde4e9e-307c-477d-9089-cae9484c8bc1/download/la-county-neighborhoods-v6.geojson'
area_map = folium.Map(location =[34.0522, -118.2437], zoom_start = 12 )
In [ ]:
In [506]:
area_map.choropleth(
geo_data=gjson2,
data=sorted_average_price,
columns=['neighbourhood_cleansed','price'],
key_on='feature.properties.name',
fill_color='YlOrRd',
fill_opacity=0.7,
line_opacity=0.2,
legend_name='Average Listing price in Los Angeles'
)
area_map
Out[506]:
In [ ]:
In [ ]:
In [ ]:
In [399]:
airbnb_nomissing['id'].value_counts().shape
In [146]:
len(airbnb_nomissing['id'])
Out[146]:
In [161]:
airbnb_nomissing.zipcode.dtypes
Out[161]:
In [162]:
zipcode_priceavg = airbnb_nomissing[['zipcode','price']].groupby('zipcode').agg(lambda x: x.mean())
#missing_stats.sort_values(by = "missing_num", ascending =False, inplace = True)
In [165]:
zipcode_priceavg.sort_values(by = 'price',ascending = False, inplace = True)
In [166]:
zipcode_priceavg.head()
Out[166]:
In [167]:
if "default zip code" in airbnb_nomissing['zipcode']:
print("yes")
In [171]:
airbnb_nomissing[airbnb_nomissing['zipcode'] == '-- default zip code --'][['zipcode','price',
'property_type','room_type']]
Out[171]:
In [356]:
airbnb_nomissing.head()
Out[356]:
In [ ]: